package batch.udb;

import org.apache.commons.lang.time.DateFormatUtils;
import org.apache.commons.lang.ObjectUtils;
import org.apache.log4j.*;

import java.sql.*;
import java.io.*;
import java.util.Date;
import java.util.Calendar;

public class makeFinalWapTransFileForGbrCRM
{
    private static String sTmpDate = "";

    public makeFinalWapTransFileForGbrCRM() {
        ;
    }

    public static void main(String [] args) {
        Logger.getLogger("udb.makeFinalWapTransFileForGbrCRM").info("( * ) Start.");

        if(args.length == 1){
            sTmpDate = args[0];
        }

        makeFinalWapTransFileForGbrCRM mkUser = new makeFinalWapTransFileForGbrCRM();
        mkUser.startMake();

        Logger.getLogger("udb.makeFinalWapTransFileForGbrCRM").info("( * ) End.");
    }

    //Make a log file and a data file.
    public void startMake(){
        Connection   conn = null;
        Statement    stmt = null;
        ResultSet    rs   = null;
        OutputStreamWriter oswFile = null;

        Calendar calen = Calendar.getInstance();

        String TODAY     = "";
        String YESTERDAY = "";

        Date ye1day = null;

        if(sTmpDate.equals("") || sTmpDate == null){
            calen.add(Calendar.DATE, -1);
            ye1day = calen.getTime();

            TODAY = DateFormatUtils.format(new Date(), "yyyyMMdd");
            YESTERDAY = DateFormatUtils.format(ye1day, "yyyyMMdd");
        }
        else{
            TODAY = sTmpDate;

            java.text.SimpleDateFormat sformat = new java.text.SimpleDateFormat("yyyyMMdd");
            java.util.Date dTmpDate = sformat.parse(TODAY, new java.text.ParsePosition(0));

            calen.setTime(dTmpDate);
            calen.add(Calendar.DATE, -1);
            ye1day = calen.getTime();

            YESTERDAY = DateFormatUtils.format(ye1day, "yyyyMMdd");
        }

        String fileNm  = "/home01/sfc/batch/udb/data/fin_"  + TODAY;

        int cnt = 0;
        int rsCnt = 0;

        StringBuffer sbTemp = new StringBuffer();
        StringBuffer sQuery = new StringBuffer()
            .append("SELECT LOG_NO ,                                                                            \n")
            .append("  MSISDN F2 ,                                                                              \n")
            .append("  X.CREATED_YMDHMS F1 ,                                                                    \n")
            .append("  X.OP_CD ,                                                                                \n")
            .append("  (                                                                                        \n")
            .append("    SELECT CP_NM                                                                           \n")
            .append("    FROM G2_CP                                                                             \n")
            .append("    WHERE CP_ID = X.CP_ID) CP_ID ,                                                         \n")
            .append("  X.CTYPE_CD F3 ,                                                                          \n")
            .append("  TITLE F8 ,                                                                               \n")
            .append("  NVL(X.HIGHLIGHT_TITLE, X.CATEGORY_TITLE) F9,                                             \n")
            .append("  X.PRICE AS F5 ,                                                                          \n")
            .append("  DECODE(DOWN_STATUS, '001', 'Y' , 'N') DOWN_STATUS ,                                      \n")
            .append("  DECODE(BILL_STATUS, '0' , 'Y' , 'M', 'M', 'N') F4 ,                                      \n")
            .append("  X.CONTENT_ID                                                                             \n")
            .append("FROM (                                                                                     \n")
            .append("    SELECT A.* ,                                                                           \n")
            .append("      (                                                                                    \n")
            .append("        SELECT CATE_NM                                                                     \n")
            .append("        FROM G2_CATEGORY                                                                   \n")
            .append("        WHERE CATE_ID = A.CATEGORY_ID                                                      \n")
            .append("          AND '" + YESTERDAY + "240000' >= START_YMDHMS                                             \n")
            .append("          AND '" + YESTERDAY + "000000' <= END_YMDHMS ) CATEGORY_TITLE ,                   \n")
            .append("      (                                                                                    \n")
            .append("        SELECT TITLE                                                                       \n")
            .append("        FROM G2_HIGHLIGHT                                                                  \n")
            .append("        WHERE HIGHT_ID = A.CATEGORY_ID                                                     \n")
            .append("          AND '" + YESTERDAY + "240000' >= START_YMDHMS                                    \n")
            .append("          AND '" + YESTERDAY + "000000' <= END_YMDHMS ) HIGHLIGHT_TITLE ,                  \n")
            .append("      B.DELIVERY_STATUS AS BILL_STATUS                                                     \n")
            .append("    FROM G2_DOWN_LOG_GBR A ,                                                               \n")
            .append("      G2_IPX_TRANS_GBR B                                                                   \n")
            .append("    WHERE A.LOG_NO = B.LOG_NO                                                              \n")
            .append("      AND A.PRICE <> 0                                                                     \n")
            .append("      AND A.CREATED_YMDHMS BETWEEN '" + YESTERDAY + "000000' AND '" + YESTERDAY + "240000' \n")
            .append("    UNION ALL                                                                              \n")
            .append("    SELECT A.* ,                                                                           \n")
            .append("      (                                                                                    \n")
            .append("        SELECT CATE_NM                                                                     \n")
            .append("        FROM G2_CATEGORY                                                                   \n")
            .append("        WHERE CATE_ID = A.CATEGORY_ID                                                      \n")
            .append("          AND '" + YESTERDAY + "240000' >= START_YMDHMS                                    \n")
            .append("          AND '" + YESTERDAY + "000000' <= END_YMDHMS ) CATEGORY_TITLE ,                   \n")
            .append("      (                                                                                    \n")
            .append("        SELECT TITLE                                                                       \n")
            .append("        FROM G2_HIGHLIGHT                                                                  \n")
            .append("        WHERE HIGHT_ID = A.CATEGORY_ID                                                     \n")
            .append("          AND '" + YESTERDAY + "240000' >= START_YMDHMS                                    \n")
            .append("          AND '" + YESTERDAY + "000000' <= END_YMDHMS ) HIGHLIGHT_TITLE ,                  \n")
            .append("      CONCAT('', B.FINALIZE_RESPONSE_CODE) AS BILL_STATUS                                  \n")
            .append("    FROM G2_DOWN_LOG_GBR A ,                                                               \n")
            .append("      G2_IPX_SESSION_GBR B                                                                 \n")
            .append("    WHERE A.LOG_NO = B.DOWNLOAD_LOG_NO                                                     \n")
            .append("      AND A.PRICE <> 0                                                                     \n")
            .append("      AND A.CREATED_YMDHMS BETWEEN '" + YESTERDAY + "000000' AND '" + YESTERDAY + "240000' \n")
            .append("    UNION ALL                                                                              \n")
            .append("    SELECT A.* ,                                                                           \n")
            .append("      (                                                                                    \n")
            .append("        SELECT CATE_NM                                                                     \n")
            .append("        FROM G2_CATEGORY                                                                   \n")
            .append("        WHERE CATE_ID = A.CATEGORY_ID                                                      \n")
            .append("          AND '" + YESTERDAY + "240000' >= START_YMDHMS                                    \n")
            .append("          AND '" + YESTERDAY + "000000' <= END_YMDHMS ) CATEGORY_TITLE ,                   \n")
            .append("      (                                                                                    \n")
            .append("        SELECT TITLE                                                                       \n")
            .append("        FROM G2_HIGHLIGHT                                                                  \n")
            .append("        WHERE HIGHT_ID = A.CATEGORY_ID                                                     \n")
            .append("          AND '" + YESTERDAY + "240000' >= START_YMDHMS                                    \n")
            .append("          AND '" + YESTERDAY + "000000' <= END_YMDHMS ) HIGHLIGHT_TITLE ,                 \n")
            .append("      'M' AS BILL_STATUS                                                                   \n")
            .append("    FROM G2_DOWN_LOG_GBR A                                                                 \n")
            .append("    WHERE CREATED_YMDHMS BETWEEN '" + YESTERDAY + "000000' AND '" + YESTERDAY + "240000'   \n")
            .append("      AND PRICE = 0) X ,                                                                   \n")
            .append("  G2_VIEW_CONTENT_ID_MAIN Y                                                                \n")
            .append("WHERE X.CONTENT_ID = Y.CONTENT_ID(+)                                                       \n")
            .append("ORDER BY X.CREATED_YMDHMS                                                                  \n");
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            conn = DriverManager.getConnection("jdbc:oracle:thin:@eu-anydb:1521:ORAEU", "gsms", "any02gs");
            conn.setAutoCommit(true);
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sQuery.toString());
            rsCnt = rs.getMetaData().getColumnCount();

            oswFile = new OutputStreamWriter(new FileOutputStream(fileNm, false), "ISO-8859-1");

            while(rs.next()){
                sbTemp.setLength(0);

                for(int i=1;i<=rsCnt;i++) {
                    sbTemp.append(ObjectUtils.toString(rs.getObject(i), ""));

                    if( i == rsCnt ){
                        sbTemp.append("\n");
                    }
                    else{
                        sbTemp.append("|");
                    }
                }

                oswFile.write(sbTemp.toString());
                cnt++;

                if(cnt % 500 == 0){
                    Logger.getLogger("udb.makeFinalWapTransFileForGbrCRM").info("here we are == " + cnt);
                }
            }

            Logger.getLogger("udb.makeFinalWapTransFileForGbrCRM").info("Total Count : " + cnt);
        }
        catch(Exception ex) {
            ex.printStackTrace();

            Logger.getLogger("udb.makeFinalWapTransFileForGbrCRM").info(ex.toString());
            StackTraceElement[] stElement = ex.getStackTrace();
            for(int p=0; p<stElement.length;p++){
                Logger.getLogger("udb.makeFinalWapTransFileForGbrCRM").info(stElement[p].toString());
            }
        }
        finally {
            if (rs   != null) try { rs.close(); }   catch (Exception e) { e.printStackTrace(); }
            if (stmt != null) try { stmt.close(); } catch (Exception e) { e.printStackTrace(); }
            if (conn != null) try { conn.close(); } catch (Exception e) { e.printStackTrace(); }
            if (oswFile != null) try { oswFile.close(); } catch (Exception e) { e.printStackTrace(); }
        }
    }
}